Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Part VII
References

Appendix A Review of Tuning Guidelines
B Quick Reference
C Flowcharts
D Glossary
E Oracle Tuning Parameters
F Contents of the CD-ROM

Appendix A
Review of Tuning Guidelines

This appendix is intended as a quick reference to many of the topics presented throughout this book.

RDBMS Tuning

The following sections review many of the concepts presented in Part II, “Tuning the Server.” They review some of the areas of importance and how to find and overcome performance bottlenecks. The following sections relate primarily to the tuning parameters for the OS and Oracle.

SGA

The System Global Area (SGA) contains the shared pool, the redo log buffer, and the database block buffers.

Shared Pool

The shared pool contains the library cache, the data dictionary cache, and the shared session area (with the multithreaded server).

Library Cache

The library cache contains the shared SQL and PL/SQL areas. You can improve performance by both increasing the cache-hit rate in the library cache and by speeding access to the library cache by holding infrequently used SQL statements in cache longer.

The V$LIBRARYCACHE table contains statistics about how well you are using the library cache. The important columns to view in this table are PINS and RELOADS:

  PINS: The number of times the item in the library cache was executed.
  RELOADS: The number of times the library cache missed and the library object was reloaded.

A low number of reloads relative to the number of executions indicates a high cache-hit rate.

Data Dictionary Cache

The data dictionary cache contains a set of tables and views that Oracle uses as a reference to the database. It is here that Oracle stores information about both the logical and physical structure of the database.

To check the efficiency of the data dictionary cache, check the cache-hit rate. Statistics for the data dictionary cache are stored in the dynamic performance table V$ROWCACHE (the data dictionary cache is sometimes known as the row cache). The important columns to view in this table are GETS and GETMISSES:

  GETS: The total number of requests for the particular item.
  GETMISSES: The total number of requests resulting in cache misses.

A low number of cache misses are expected, especially during startup, when the cache has not been populated.

Shared Session Information

In a multithreaded server configuration, the session information is also stored in the shared pool. This information includes the private SQL areas as well as sort areas. It is important to make sure that you do not run out of memory for this shared session information.

To determine whether you need to increase space for these shared sessions, you can extract the sum of memory allocated for all sessions and the maximum amount of memory allocated for sessions from the dynamic performance table V$SESSTAT. If the maximum amount of memory used is high, it may be necessary to increase the size of the shared pool. Because the shared pool is used for other functions as well (such as the library cache and the data dictionary cache), it is a good idea to increase the size of the shared pool to accommodate the additional memory usage. If you have enough memory in your system, increase the shared pool by the maximum amount of memory used by the shared server processes. If you have a limited amount of memory, use the sum of memory allocated to sessions (obtained when an average number of users were connected and running) as the basis for the amount of memory for the shared pool.

Database Block Buffer Cache

Probably the most important Oracle cache in the system is the buffer cache. The buffer cache makes up the majority of the Oracle SGA and is used for every query and update in the system.

The statistics for the buffer cache are kept in the dynamic performance table V$SYSSTAT. The important columns to view in this table are listed here:

  PHYSICAL READS: The total number of requests that result in a disk access. This is a cache miss.
  DB BLOCK GETS: The number of requests for blocks in current mode.
  CONSISTENT GETS: The number of requests for blocks in consistent mode. Buffers are typically retrieved in consistent mode for queries.

The sum of the values in DB BLOCK GETS and CONSISTENT GETS represents the total number of requests for data.

The cache-hit ratio is determined using this formula:

Cache Hit Ratio = 1 - ( PHYSICAL READS / ( DB BLOCK GETS + CONSISTENT GETS))

The block buffers are the most important area of the SGA and must be tuned because of the large effect they have on the system and the amount of resources they consume.

Performance Enhancements

I prefer to separate the performance-enhancement options from the general tuning of Oracle. Performance enhancements tend to be things that may or may not help your configuration and application; in fact, they may sometimes hurt if you’re not careful. On the other hand, tuning parameters always helps, based on correct interpretation of Oracle statistics. The following sections review a few of the enhancements you have seen throughout the book.

Block Size

Depending on your configuration and data access patterns, you may be able to benefit from using a larger block size. With a larger block size, under certain circumstances, you get the benefit of less wasted space and more efficient I/O. Here are a few guidelines that may help you decide whether changing the size of DB_BLOCK_SIZE can benefit you:

  OLTP systems benefit from smaller blocks. If your application is OLTP in nature, you will not benefit from larger blocks. OLTP data typically fits well in the default block size; larger blocks would unnecessarily eject blocks from the SGA.
  DSS systems benefit from larger blocks. In the DSS system in which table scans are common, retrieving more data at a time results in a performance increase.
  Larger databases benefit from larger blocks. Larger databases see a space benefit from less wastage per block.
  Databases with large rows will benefit from larger blocks. If your rows are extremely large (such as images or text) and don’t fit in the default block, you will see a definite benefit from a larger block size.

Because changing the block size unnecessarily causes increased I/O overhead, this change does carry some risk. Change the block size with caution.

Clusters

A cluster, sometimes called an index cluster, is an optional method of storing tables in an Oracle database. Within a cluster, multiple related tables are stored together to improve access time to the related items. Clusters are useful in cases where related data is often accessed together. The existence of a cluster is transparent to users and to applications; the cluster affects only how data is stored.

A cluster can be useful for tables in which data is primarily accessed together in a join. In such situations, the reduced I/O needed to bring the additional data into the SGA and the fact that the data is already cached can be a big advantage.

However, for situations in which the tables have a large number of INSERT statements or the data is not frequently accessed together, a cluster is not useful and should not be used.

Do not cluster tables if full-table scans are often performed on only one of the tables in the cluster. The additional space required by the cluster and the additional I/O reduces performance.

Reduce Fragmentation

Fragmentation is the condition that occurs when pieces of the database are no longer contiguous. Fragmentation can consist of disk fragmentation or tablespace fragmentation. Both of these types of fragmentation usually affect performance.

Disk fragmentation usually causes multiple I/Os to occur when one I/O would have been sufficient (for example, with chained or migrated rows). Disk fragmentation can also be caused when the extents that make up the database segments are noncontiguous, which can happen with excessive dynamic growth.

Tablespace fragmentation is caused by the dropping and creation of segments. This can create large free areas between segments, which result in the inefficient use of space and excessive disk seeks over the empty areas. Tablespace fragmentation can also prevent Oracle from taking advantage of multiblock reads.

One way to eliminate fragmentation is to export the table or tablespace data, remove and re-create the table or tablespace, and import the data. By eliminating fragmentation, you can reduce excessive I/Os and CPU usage, streamlining data access. Any overhead and unnecessary I/Os you can reduce will improve system performance.

Hash Clusters

A hash cluster is similar to a cluster except that it uses a hash function rather than an index to reference the cluster key. A hash cluster stores the data based on the result of a hash function. The hash function is a numeric function that determines the data block in the cluster based on the value of the cluster key.

To achieve good performance from a hash cluster, you must meet the following criteria:

  The cluster key value is unique
  The majority of queries are equality queries on the cluster key
  The size of the table is static (very little growth occurs)
  The value of the cluster key does not change

If you can take advantage of hashing by meeting these strict criteria, you will see very good performance. Hashing is extremely efficient under the right conditions; however, having a hash cluster under the wrong conditions can cause some performance degradation.

Indexes

An index, like the index in this book, is an optional structure designed to help you achieve faster access to your data. When optimally configured and used, indexes can significantly reduce I/O to the data files and greatly improve performance. You must first decide whether an index is appropriate for the data and access patterns in your particular system. Having decided to use an index, you must decide which columns to index. Indexing appropriately can greatly improve performance by reducing I/Os and speeding access times.

Careful planning and periodic testing with SQL Trace can lead to very effective use of indexes, with optimal performance being the outcome.

Multiblock Reads

When performing table scans, Oracle has the capability to read more than one block at a time, thus speeding I/Os. By reading more than one block at a time, a larger chunk of data can be read from the disk, eliminating some disk seeks. By reducing disk seeks and reading larger blocks, both I/O and CPU overhead are reduced.

The amount of data read in a multiblock read is specified by the Oracle initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. The value for this parameter should always be set high because there is rarely any disadvantage in doing so. The size of the individual I/O requests depends on both DB_FILE_MULTIBLOCK_READ_COUNT and DB_BLOCK_SIZE. A good value for multiblock reads is 64K.

Multiblock Writes

Multiblock writes are similar in nature to multiblock reads and have many of the same requirements. The multiblock write feature is new with Oracle version 7.3. Under certain conditions, you can now perform multiblock writes.

Multiblock writes are available through the direct path loader as well as through sorts and index creations. As with multiblock reads, multiblock writes reduce I/O and CPU overhead by writing multiple database blocks in one larger I/O operation.

The amount of data written in a multiblock write is specified by the Oracle initialization parameter DB_FILE_MULTIBLOCK_WRITE_COUNT. The size of the individual I/O requests depends on both DB_FILE_MULTIBLOCK_WRITE_COUNT and DB_BLOCK_SIZE. As with multiblock reads, a good value is 64K.

Oracle Parallel Query Option

The Oracle Parallel Query option makes it possible for some Oracle functions to be processed by multiple server processes. The functions affected are queries, index creation, data loading, and recovery. For each of these functions, the general principle is the same: Keep the processing going while Oracle is waiting for I/O.

For most queries, the time spent waiting for the data to be retrieved from disk usually overshadows the amount of time actually spent processing the results. With the Parallel Query option, you can compensate for this “wasted time” by using several server processes to execute the query. While one process is waiting for I/Os to complete, other processes can execute. If you are running on a Symmetric Multiprocessor (SMP) computer, a cluster, or a Massively Parallel Processing (MPP) machine, you can take maximum advantage of the Parallel Query option.

The amount of parallelism can be tuned with several of the Oracle initialization parameters:


Parameter Description

PARALLEL_DEFAULT_MAX_SCANS Specifies the maximum number of query servers to used by default for a query. This valued is used only if no value is specified in a PARALLEL hint or in the PARALLEL definition clause. This parameter limits the number of query servers used by default when the value of PARALLEL_DEFAULT_SCANSIZE is used by the query coordinator.
PARALLEL_DEFAULT_SCANSIZE Specifies the number of query servers to be used for a particular table. The size of the table divided by PARALLEL_DEFAULT_SCANSIZE determines the number of query servers, up to PARALLEL_DEFAULT_MAX_SCANS.
PARALLEL_MAX_SERVERS Maximum number of query servers or parallel recovery processes available for this instance.
RECOVERY_PARALLELISM The number of processes to be used for instance or media recovery. A large value can greatly reduce instance recovery time. A value of zero or 1 indicates that parallel recovery will not be done and that recovery will be serial. A good value for this parameter is in the range of the number of disks you have (up to 50).

I am a real fan of the Parallel Query option. I have seen great improvements from the use of parallel queries as well as dramatic reductions in recovery time when the parallel recovery feature is used.

Oracle Parallel Server Option

The Oracle Parallel Server option is one of the most innovative and impressive options available from Oracle. With the Parallel Server option, you can cluster several computers together using a shared-disk subsystem and have multiple Oracle instances access the same database. If your application is suitable, you can see very good scalability from adding additional computers.

The Oracle Parallel Server option uses a sophisticated locking mechanism in conjunction with a shared-disk subsystem to allow multiple instances to access the same data. If you have an application that can take advantage of the Oracle Parallel Server architecture, you should see some very good performance improvements.

The two areas that can most influence the performance of your parallel server system are data partitioning and PCM lock management. Both of these can make a huge difference in the performance of your system.

  Partitioning. By properly partitioning your data to reduce lock traffic and contention for blocks between servers, you can enhance performance. Try to balance your users so that the users accessing the same tables are on the same machine; doing so can reduce contention for locks.
  PCM locks. By carefully managing the number of locks on each table, you can enhance performance. Tables with a lot of traffic between nodes should have more locks than tables with less contention. By balancing the number of locks, you can reduce overhead.


TIP:  By taking advantage of read-only tablespaces when applicable, you can reduce the number of PCM locks in your system. Because read-only tablespaces do not allow updates, no locking is necessary.

Spin Counts

Multiprocessor environments may benefit by tuning the parameter SPIN_COUNT. Under normal circumstances, if a latch is not available, the process sleeps for a while and then wakes up to try the latch again. If you are on a multiprocessor system, it is likely that the process holding the latch is currently processing on another CPU and will be finished in a short time. By setting SPIN_COUNT to a value greater than zero, the process spins while counting down from SPIN_COUNT to zero. If the latch is still not available, the process goes to sleep.

Setting SPIN_COUNT can hurt performance if you’re not careful. This parameter should be set only for multiprocessor computers and should be monitored for effectiveness. A good value to try is 2000. The value of SPIN COUNT specifies how many times the process will spin before putting itself to sleep. Because the speed of processors varies, the time it takes to spin also varies, but the speed of the other process holding the desired resource also vary with the speed of the processor.

OS Tuning

OS tuning is very specific and dependent on the OS you are running. There are, however, some general guidelines you can follow.

OS Tuning Goals

The general goal in tuning the server operating system is to simply provide the resources Oracle needs to function optimally. Here’s a list of several areas that need attention:


OS Area Comments

Memory Enough memory should be allocated to Oracle to hold the entire SGA in physical memory. If your SGA pages out, performance is severely degraded. Paging out of user processes should also be avoided.
I/O The I/O subsystem should be tuned to have the least amount of overhead possible. By reducing overhead, the performance of the entire system is increased.
Processes The system should be configured to handle the required number of processes necessary to support your users.

These and other OS features should be used with the goal of reducing system overhead. Any extra CPU time spent in the OS is CPU time not spent processing Oracle.

OS Features

You should make use of any OS feature that can reduce overhead. Although these features vary from platform to platform and are OS specific, some common areas are listed here:


Feature Comment

Post-wait semaphore This feature is available on some platforms and reduces some of the overhead associated with traditional semaphores and scheduling.
Scheduling parameters Some OSes allow you to adjust scheduling to reduce the amount of preemption in the system. If available, use this feature.
Cache affinity Cache affinity is available in some operating systems and should be used with caution. Although some application such as decision support can benefit, OLTP may suffer slightly from the effects of cache affinity.
Asynchronous I/O (AIO) Most operating systems have AIO available; you should use it. The use of AIO reduces I/O processing overhead.

All these OS features were designed to reduce excess overhead that takes away from user processing. By optimizing your OS to reduce overhead, you can enhance total system throughput.

I/O Tuning

The I/O system should be designed and implemented with the following goals in mind:

  Isolate sequential I/O. By isolating sequential I/O so that it is purely sequential to the disk, you can greatly enhance throughput. Any random I/O to these disks degrades performance. Writes to the redo log are sequential.
  Spread out random I/O. Random I/O performance can be increased by adding more disk drives to the system and spreading out the I/Os among the disks. I/Os to the data files are typically random (especially in OLTP systems).

By following these guidelines and planning your system so that your disk drives can support the amount of disk I/O demanded of them, I/O should not be a problem.

System Design

Part III, “Configuring the System,” looked at several types of systems and determined the data access patterns, the system load, and optional features that can enhance performance. The systems that were reviewed are listed here:


System Description

OLTP Characteristics include many users, high I/O rates, much random I/O, and response-time constraints. The Oracle Parallel Server option is possibly a win for OLTP systems.
Batch Characteristics include system load-time constraints, index build-time constraints, and long-running queries. The Oracle Parallel Query option can benefit the typical batch system. Large block sizes also help.
DSS Characteristics include system load-time constraints, index build-time constraints, and long-running queries. The Oracle Parallel Query option definitely benefits the typical DSS system. Large block sizes and multiblock reads also help. The Oracle Parallel Server option may also benefit the DSS system.
Data warehouse The characteristics of a data warehouse system are much like those of a super DSS system: a huge amount of data, large queries, and heavy I/O usage. Both the Parallel Query and Parallel Server options may benefit the data warehouse system.
BLOBs BLOBs require significant I/O and response time. Data-feed interruption cannot be tolerated. Large block sizes are a must.
Parallel server The Oracle parallel server system can be enhanced by carefully partitioning tasks and allocating PCM locks efficiently. Because the parallel server system involves two or more systems working together, balancing transactions and functions is crucial.
Optimal backup If backup and recovery time is critical, there are several ways your system can be configured to optimize for these tasks.
Miscellaneous Other Oracle products (Oracle Financials, Oracle WebSystem, and others) have their own unique characteristics and types of enhancements.

By looking at various types of systems and examining their characteristics, Part III, “Configuring the System,” gave you a feel for how these systems operate. By understanding the systems, you have a better idea of how to tune them for optimal performance.

Application Tuning

Part IV, “Tuning SQL,” looked at tuning the application, which mainly involves tuning the SQL statements themselves. Several chapters explained how to tune specific SQL statements and how to use Oracle features to your advantage. Here is a list of some of the important topics discussed in Part IV:

  Using EXPLAIN PLAN. By using EXPLAIN PLAN, you can analyze the execution plan the optimizer has chosen for the SQL statement.
  Using SQL Trace. By using SQL Trace, you can analyze the execution of the SQL statement and determine any potential bottlenecks.
  Tuning SQL statements. You can improve performance by making SQL statements take advantage of such things as indexes, clusters, and hash clusters.
  Using the Oracle optimizer. You learned how the optimizer works and how you can best take advantage of it.
  Using procedures and packages. You learned how to use procedures, functions, and packages to improve performance.
  Providing for data integrity. You learned about the importance of data integrity and how to optimally provide for it.
  Using hints. You learned how to use hints to take advantage of information you know about the data and application.
  Miscellaneous topics. The last chapter of Part IV provided tips and miscellaneous topics concerning the tuning of SQL statements.

By now, you should have an idea about the importance of tuning your SQL statements. You should also know how to take advantage of various Oracle features to improve the performance of those statements.

Client Tuning

Part V, “Tuning the Client,” looked at the client computer itself. You learned about some of the things that can affect client performance and how to solve those problems. You also looked at how to tune the client system and how to take advantage of some popular graphical development tools. You saw how to use these tools to improve the performance of the SQL statements generated by those tools. Here is a list of some of the key topics in Part V:

  Client bottlenecks. You learned which parts of the client are likely to become bottlenecks: the application, the network, and the client hardware.
  Memory. Make sure that your client machine has enough memory to run the application. Memory is typically the only hardware performance problem on the client machine.
  CPU. As applications become increasingly more robust and feature rich, the need for faster CPUs is also increasing. You may have to upgrade your CPU if it is not fast enough.
  Network. Although network performance is usually not a problem, it can be in some situations. Make sure that your LAN segments are not overloaded.
  Use compiled applications. Some application development tools use an interpretive language. For best performance, use a compiled application.
  Use 32-bit applications. If you are running Windows NT or Windows 95, be sure that you use 32-bit applications; 16-bit applications are not as efficient on these systems.
  Use native drivers. Although using ODBC can make connections to various servers more convenient, it hurts performance. Use native drivers whenever possible.
  Optimize SQL. Even though your application may have been developed by a GUI development tool, you still may have to optimize your SQL statements.

You should now understand what affects the performance of the client machine and how to solve those problems. Part V of this book also introduced middleware products and how they can be used to improve the overall performance of your configuration.

Network Tuning

Part VI, “Tuning the Network,” looked at the capacity of the network and how you can tune it. The real way to tune the physical network is to stay within its capacity. As your needs grow, you must improve the capacity of your network.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.